package net.kesumu.yushabu.storage;

import java.sql.Timestamp;
import java.util.ArrayList;

import net.kesumu.yushabu.storage.YushabuMessageContract.YushabuMessageEntry;
import net.kesumu.yushabuserver.entity.YushabuMessage;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class YushabuMessageDbOperator {

    YushabuDbHelper mDbHelper;
    
    public YushabuMessageDbOperator(Context context) {
        mDbHelper = new YushabuDbHelper(context);
    }
    
    public long insert(YushabuMessage yushabuMessage) {
        // Gets the data repository in write mode
        SQLiteDatabase db = mDbHelper.getWritableDatabase();

        // Create a new map of values, where column names are the keys
        ContentValues values = new ContentValues();
        values.put(YushabuMessageEntry.COLUMN_NAME_MESSAGEID, yushabuMessage.getMessageId());
        values.put(YushabuMessageEntry.COLUMN_NAME_SESSIONID, yushabuMessage.getSessionId());
        values.put(YushabuMessageEntry.COLUMN_NAME_USERID, yushabuMessage.getUserId());
        values.put(YushabuMessageEntry.COLUMN_NAME_FRIENDUSERID, yushabuMessage.getFriendUserId());
        values.put(YushabuMessageEntry.COLUMN_NAME_CONTENT, yushabuMessage.getContent());
        values.put(YushabuMessageEntry.COLUMN_NAME_SENDTIME, yushabuMessage.getSendTime().toString());
        values.put(YushabuMessageEntry.COLUMN_NAME_STATE, yushabuMessage.getState());


        // Insert the new row, returning the primary key value of the new row
        long newRowId;
        newRowId = db.insert(
                            YushabuMessageEntry.TABLE_NAME,
                            null,
                            values);
        return newRowId;
    }
    
    public YushabuMessage selectByMessageId(String messageId) {
        SQLiteDatabase db = mDbHelper.getReadableDatabase();

        // Define a projection that specifies which columns from the database
        // you will actually use after this query.
        String[] projection = {
            YushabuMessageEntry._ID,
            YushabuMessageEntry.COLUMN_NAME_MESSAGEID,
            YushabuMessageEntry.COLUMN_NAME_SESSIONID,
            YushabuMessageEntry.COLUMN_NAME_USERID,
            YushabuMessageEntry.COLUMN_NAME_FRIENDUSERID,
            YushabuMessageEntry.COLUMN_NAME_CONTENT,
            YushabuMessageEntry.COLUMN_NAME_SENDTIME,
            YushabuMessageEntry.COLUMN_NAME_STATE
            };
        
        // Define 'where' part of query.
        String selection = YushabuMessageEntry.COLUMN_NAME_MESSAGEID + " = ?";
        // Specify arguments in placeholder order.
        String[] selectionArgs = { messageId };
   
        // How you want the results sorted in the resulting Cursor
        String sortOrder =
                YushabuMessageEntry.COLUMN_NAME_MESSAGEID;
   
        Cursor cursor = db.query(
            YushabuMessageEntry.TABLE_NAME,  // The table to query
            projection,                               // The columns to return
            selection,                                // The columns for the WHERE clause
            selectionArgs,                            // The values for the WHERE clause
            null,                                     // don't group the rows
            null,                                     // don't filter by row groups
            sortOrder                                 // The sort order
            );
        if(cursor.getCount()>0) {
            cursor.moveToLast();
            String itemMessageId = cursor.getString(
                    cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_MESSAGEID)
                );
            String itemSessionId = cursor.getString(
                    cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_SESSIONID)
                );
            String itemUserId = cursor.getString(
                    cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_USERID)
                );
            String itemFriendUserId = cursor.getString(
                    cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_FRIENDUSERID)
                );
            String itemContent = cursor.getString(
                    cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_CONTENT)
                );
            String itemSendTime = cursor.getString(
                    cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_SENDTIME)
                );
            String itemState = cursor.getString(
                cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_STATE)
            );
            YushabuMessage yushabuMessage = new YushabuMessage();
            yushabuMessage.setMessageId(Integer.parseInt(itemMessageId));
            yushabuMessage.setSessionId(Integer.parseInt(itemSessionId));
            yushabuMessage.setUserId(Integer.parseInt(itemUserId));
            yushabuMessage.setFriendUserId(Integer.parseInt(itemFriendUserId));
            yushabuMessage.setContent(itemContent);
            yushabuMessage.setSendTime(Timestamp.valueOf(itemSendTime));
            yushabuMessage.setState(Integer.parseInt(itemState));
            return yushabuMessage;
        }
        return null;
    }
    
    public YushabuMessage selectLatestByFriendId(String friendId) {
        SQLiteDatabase db = mDbHelper.getReadableDatabase();

        // Define a projection that specifies which columns from the database
        // you will actually use after this query.
        String[] projection = {
            YushabuMessageEntry._ID,
            YushabuMessageEntry.COLUMN_NAME_MESSAGEID,
            YushabuMessageEntry.COLUMN_NAME_SESSIONID,
            YushabuMessageEntry.COLUMN_NAME_USERID,
            YushabuMessageEntry.COLUMN_NAME_FRIENDUSERID,
            YushabuMessageEntry.COLUMN_NAME_CONTENT,
            YushabuMessageEntry.COLUMN_NAME_SENDTIME,
            YushabuMessageEntry.COLUMN_NAME_STATE
            };
        
        // Define 'where' part of query.
        String selection = "(" + YushabuMessageEntry.COLUMN_NAME_USERID + " = ?" +
                           " OR " + YushabuMessageEntry.COLUMN_NAME_FRIENDUSERID + " = ?)" + 
                           " AND " + YushabuMessageEntry.COLUMN_NAME_SESSIONID + " = 0";
        // Specify arguments in placeholder order.
        String[] selectionArgs = { friendId, friendId };
   
        // How you want the results sorted in the resulting Cursor
        String sortOrder =
                YushabuMessageEntry.COLUMN_NAME_SENDTIME;
   
        Cursor cursor = db.query(
            YushabuMessageEntry.TABLE_NAME,  // The table to query
            projection,                               // The columns to return
            selection,                                // The columns for the WHERE clause
            selectionArgs,                            // The values for the WHERE clause
            null,                                     // don't group the rows
            null,                                     // don't filter by row groups
            sortOrder                                 // The sort order
            );
        if(cursor.getCount()>0) {
            cursor.moveToLast();
            String itemMessageId = cursor.getString(
                    cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_MESSAGEID)
                );
            String itemSessionId = cursor.getString(
                    cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_SESSIONID)
                );
            String itemUserId = cursor.getString(
                    cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_USERID)
                );
            String itemFriendUserId = cursor.getString(
                    cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_FRIENDUSERID)
                );
            String itemContent = cursor.getString(
                    cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_CONTENT)
                );
            String itemSendTime = cursor.getString(
                    cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_SENDTIME)
                );
            String itemState = cursor.getString(
                cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_STATE)
            );
            YushabuMessage yushabuMessage = new YushabuMessage();
            yushabuMessage.setMessageId(Integer.parseInt(itemMessageId));
            yushabuMessage.setSessionId(Integer.parseInt(itemSessionId));
            yushabuMessage.setUserId(Integer.parseInt(itemUserId));
            yushabuMessage.setFriendUserId(Integer.parseInt(itemFriendUserId));
            yushabuMessage.setContent(itemContent);
            yushabuMessage.setSendTime(Timestamp.valueOf(itemSendTime));
            yushabuMessage.setState(Integer.parseInt(itemState));
            return yushabuMessage;
        }
        return null;
    }
    
    public YushabuMessage selectLatestBySesssionId(String sessionId) {
        SQLiteDatabase db = mDbHelper.getReadableDatabase();

        // Define a projection that specifies which columns from the database
        // you will actually use after this query.
        String[] projection = {
            YushabuMessageEntry._ID,
            YushabuMessageEntry.COLUMN_NAME_MESSAGEID,
            YushabuMessageEntry.COLUMN_NAME_SESSIONID,
            YushabuMessageEntry.COLUMN_NAME_USERID,
            YushabuMessageEntry.COLUMN_NAME_FRIENDUSERID,
            YushabuMessageEntry.COLUMN_NAME_CONTENT,
            YushabuMessageEntry.COLUMN_NAME_SENDTIME,
            YushabuMessageEntry.COLUMN_NAME_STATE
            };
        
        // Define 'where' part of query.
        String selection = YushabuMessageEntry.COLUMN_NAME_SESSIONID + " = ?";
        // Specify arguments in placeholder order.
        String[] selectionArgs = { sessionId };
   
        // How you want the results sorted in the resulting Cursor
        String sortOrder =
                YushabuMessageEntry.COLUMN_NAME_SENDTIME;
   
        Cursor cursor = db.query(
            YushabuMessageEntry.TABLE_NAME,  // The table to query
            projection,                               // The columns to return
            selection,                                // The columns for the WHERE clause
            selectionArgs,                            // The values for the WHERE clause
            null,                                     // don't group the rows
            null,                                     // don't filter by row groups
            sortOrder                                 // The sort order
            );
        if(cursor.getCount()>0) {
            cursor.moveToLast();
            String itemMessageId = cursor.getString(
                    cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_MESSAGEID)
                );
            String itemSessionId = cursor.getString(
                    cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_SESSIONID)
                );
            String itemUserId = cursor.getString(
                    cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_USERID)
                );
            String itemFriendUserId = cursor.getString(
                    cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_FRIENDUSERID)
                );
            String itemContent = cursor.getString(
                    cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_CONTENT)
                );
            String itemSendTime = cursor.getString(
                    cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_SENDTIME)
                );
            String itemState = cursor.getString(
                cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_STATE)
            );
            YushabuMessage yushabuMessage = new YushabuMessage();
            yushabuMessage.setMessageId(Integer.parseInt(itemMessageId));
            yushabuMessage.setSessionId(Integer.parseInt(itemSessionId));
            yushabuMessage.setUserId(Integer.parseInt(itemUserId));
            yushabuMessage.setFriendUserId(Integer.parseInt(itemFriendUserId));
            yushabuMessage.setContent(itemContent);
            yushabuMessage.setSendTime(Timestamp.valueOf(itemSendTime));
            yushabuMessage.setState(Integer.parseInt(itemState));
            return yushabuMessage;
        }
        return null;
    }
    
    public ArrayList<YushabuMessage> selectAllByFriendId(String friendId){
        ArrayList<YushabuMessage> yushabuMessages = new ArrayList<YushabuMessage>();
        SQLiteDatabase db = mDbHelper.getReadableDatabase();

        // Define a projection that specifies which columns from the database
        // you will actually use after this query.
        String[] projection = {
            YushabuMessageEntry._ID,
            YushabuMessageEntry.COLUMN_NAME_MESSAGEID,
            YushabuMessageEntry.COLUMN_NAME_SESSIONID,
            YushabuMessageEntry.COLUMN_NAME_USERID,
            YushabuMessageEntry.COLUMN_NAME_FRIENDUSERID,
            YushabuMessageEntry.COLUMN_NAME_CONTENT,
            YushabuMessageEntry.COLUMN_NAME_SENDTIME,
            YushabuMessageEntry.COLUMN_NAME_STATE
            };
        
        // Define 'where' part of query.
        String selection = "(" + YushabuMessageEntry.COLUMN_NAME_USERID + " = ?" +
                           " OR " + YushabuMessageEntry.COLUMN_NAME_FRIENDUSERID + " = ?)" + 
                           " AND " + YushabuMessageEntry.COLUMN_NAME_SESSIONID + " = 0";
        // Specify arguments in placeholder order.
        String[] selectionArgs = { friendId, friendId };
   
        // How you want the results sorted in the resulting Cursor
        String sortOrder =
                YushabuMessageEntry.COLUMN_NAME_SENDTIME;
   
        Cursor cursor = db.query(
            YushabuMessageEntry.TABLE_NAME,  // The table to query
            projection,                               // The columns to return
            selection,                                // The columns for the WHERE clause
            selectionArgs,                            // The values for the WHERE clause
            null,                                     // don't group the rows
            null,                                     // don't filter by row groups
            sortOrder                                 // The sort order
            );
        if(cursor.getCount()>0) {
            cursor.moveToFirst();
            do{
                String itemMessageId = cursor.getString(
                        cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_MESSAGEID)
                    );
                String itemSessionId = cursor.getString(
                        cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_SESSIONID)
                    );
                String itemUserId = cursor.getString(
                        cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_USERID)
                    );
                String itemFriendUserId = cursor.getString(
                        cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_FRIENDUSERID)
                    );
                String itemContent = cursor.getString(
                        cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_CONTENT)
                    );
                String itemSendTime = cursor.getString(
                        cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_SENDTIME)
                    );
                String itemState = cursor.getString(
                    cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_STATE)
                );
                YushabuMessage yushabuMessage = new YushabuMessage();
                yushabuMessage.setMessageId(Integer.parseInt(itemMessageId));
                yushabuMessage.setSessionId(Integer.parseInt(itemSessionId));
                yushabuMessage.setUserId(Integer.parseInt(itemUserId));
                yushabuMessage.setFriendUserId(Integer.parseInt(itemFriendUserId));
                yushabuMessage.setContent(itemContent);
                yushabuMessage.setSendTime(Timestamp.valueOf(itemSendTime));
                yushabuMessage.setState(Integer.parseInt(itemState));
                yushabuMessages.add(yushabuMessage);
            }while(cursor.moveToNext());
        }
        return yushabuMessages;
    }
    
    public ArrayList<YushabuMessage> selectAllBySessionId(String sessionId){
        ArrayList<YushabuMessage> yushabuMessages = new ArrayList<YushabuMessage>();
        SQLiteDatabase db = mDbHelper.getReadableDatabase();

        // Define a projection that specifies which columns from the database
        // you will actually use after this query.
        String[] projection = {
            YushabuMessageEntry._ID,
            YushabuMessageEntry.COLUMN_NAME_MESSAGEID,
            YushabuMessageEntry.COLUMN_NAME_SESSIONID,
            YushabuMessageEntry.COLUMN_NAME_USERID,
            YushabuMessageEntry.COLUMN_NAME_FRIENDUSERID,
            YushabuMessageEntry.COLUMN_NAME_CONTENT,
            YushabuMessageEntry.COLUMN_NAME_SENDTIME,
            YushabuMessageEntry.COLUMN_NAME_STATE
            };
        
        // Define 'where' part of query.
        String selection = YushabuMessageEntry.COLUMN_NAME_SESSIONID + " = ?";
        // Specify arguments in placeholder order.
        String[] selectionArgs = { sessionId };
   
        // How you want the results sorted in the resulting Cursor
        String sortOrder =
                YushabuMessageEntry.COLUMN_NAME_SENDTIME;
   
        Cursor cursor = db.query(
            YushabuMessageEntry.TABLE_NAME,  // The table to query
            projection,                               // The columns to return
            selection,                                // The columns for the WHERE clause
            selectionArgs,                            // The values for the WHERE clause
            null,                                     // don't group the rows
            null,                                     // don't filter by row groups
            sortOrder                                 // The sort order
            );
        if(cursor.getCount()>0) {
            cursor.moveToFirst();
            do{
                String itemMessageId = cursor.getString(
                        cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_MESSAGEID)
                    );
                String itemSessionId = cursor.getString(
                        cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_SESSIONID)
                    );
                String itemUserId = cursor.getString(
                        cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_USERID)
                    );
                String itemFriendUserId = cursor.getString(
                        cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_FRIENDUSERID)
                    );
                String itemContent = cursor.getString(
                        cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_CONTENT)
                    );
                String itemSendTime = cursor.getString(
                        cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_SENDTIME)
                    );
                String itemState = cursor.getString(
                    cursor.getColumnIndexOrThrow(YushabuMessageEntry.COLUMN_NAME_STATE)
                );
                YushabuMessage yushabuMessage = new YushabuMessage();
                yushabuMessage.setMessageId(Integer.parseInt(itemMessageId));
                yushabuMessage.setSessionId(Integer.parseInt(itemSessionId));
                yushabuMessage.setUserId(Integer.parseInt(itemUserId));
                yushabuMessage.setFriendUserId(Integer.parseInt(itemFriendUserId));
                yushabuMessage.setContent(itemContent);
                yushabuMessage.setSendTime(Timestamp.valueOf(itemSendTime));
                yushabuMessage.setState(Integer.parseInt(itemState));
                yushabuMessages.add(yushabuMessage);
            }while(cursor.moveToNext());
        }
        return yushabuMessages;
    }
    
    public int getUnreadedMessageNumberByFriendId(String friendId) {
        int count=0;
        SQLiteDatabase db = mDbHelper.getReadableDatabase();

        // Define a projection that specifies which columns from the database
        // you will actually use after this query.
        String[] projection = {
            YushabuMessageEntry._ID,
            YushabuMessageEntry.COLUMN_NAME_MESSAGEID,
            YushabuMessageEntry.COLUMN_NAME_SESSIONID,
            YushabuMessageEntry.COLUMN_NAME_USERID,
            YushabuMessageEntry.COLUMN_NAME_FRIENDUSERID,
            YushabuMessageEntry.COLUMN_NAME_CONTENT,
            YushabuMessageEntry.COLUMN_NAME_SENDTIME,
            YushabuMessageEntry.COLUMN_NAME_STATE
            };
        
        // Define 'where' part of query.
        String selection = YushabuMessageEntry.COLUMN_NAME_USERID + " = ? AND "
                            + YushabuMessageEntry.COLUMN_NAME_STATE + " = ?";
        // Specify arguments in placeholder order.
        String[] selectionArgs = { friendId, String.valueOf(YushabuMessageContract.STATE_UNREADED) };
   
        // How you want the results sorted in the resulting Cursor
        String sortOrder =
                YushabuMessageEntry.COLUMN_NAME_MESSAGEID;
   
        Cursor cursor = db.query(
            YushabuMessageEntry.TABLE_NAME,  // The table to query
            projection,                               // The columns to return
            selection,                                // The columns for the WHERE clause
            selectionArgs,                            // The values for the WHERE clause
            null,                                     // don't group the rows
            null,                                     // don't filter by row groups
            sortOrder                                 // The sort order
            );
        if(cursor.getCount()>0) {
            cursor.moveToFirst();
            do{
                count++;
            }while(cursor.moveToNext());
        }
        return count;
    }
    
    public int getUnreadedMessageNumberBySessionId(String sessionId) {
        int count=0;
        SQLiteDatabase db = mDbHelper.getReadableDatabase();

        // Define a projection that specifies which columns from the database
        // you will actually use after this query.
        String[] projection = {
            YushabuMessageEntry._ID,
            YushabuMessageEntry.COLUMN_NAME_MESSAGEID,
            YushabuMessageEntry.COLUMN_NAME_SESSIONID,
            YushabuMessageEntry.COLUMN_NAME_USERID,
            YushabuMessageEntry.COLUMN_NAME_FRIENDUSERID,
            YushabuMessageEntry.COLUMN_NAME_CONTENT,
            YushabuMessageEntry.COLUMN_NAME_SENDTIME,
            YushabuMessageEntry.COLUMN_NAME_STATE
            };
        
        // Define 'where' part of query.
        String selection = YushabuMessageEntry.COLUMN_NAME_SESSIONID + " = ? AND " 
                                + YushabuMessageEntry.COLUMN_NAME_STATE + " = ? ";
        // Specify arguments in placeholder order.
        String[] selectionArgs = { sessionId, String.valueOf(YushabuMessageContract.STATE_UNREADED) };
   
        // How you want the results sorted in the resulting Cursor
        String sortOrder =
                YushabuMessageEntry.COLUMN_NAME_MESSAGEID;
   
        Cursor cursor = db.query(
            YushabuMessageEntry.TABLE_NAME,  // The table to query
            projection,                               // The columns to return
            selection,                                // The columns for the WHERE clause
            selectionArgs,                            // The values for the WHERE clause
            null,                                     // don't group the rows
            null,                                     // don't filter by row groups
            sortOrder                                 // The sort order
            );
        if(cursor.getCount()>0) {
            cursor.moveToFirst();
            do{
                count++;
            }while(cursor.moveToNext());
        }
        return count;
    }
    
    public int updateReadedMessageByMessageId(String messageId) {
        SQLiteDatabase db = mDbHelper.getReadableDatabase();

         // New value for one column
         ContentValues values = new ContentValues();
         values.put(YushabuMessageEntry.COLUMN_NAME_STATE, YushabuMessageContract.STATE_READED);
    
         // Which row to update, based on the ID
         String selection = YushabuMessageEntry.COLUMN_NAME_MESSAGEID + " = ?";
         String[] selectionArgs = { messageId };
    
         int count = db.update(
             YushabuMessageEntry.TABLE_NAME,
             values,
             selection,
             selectionArgs);
        return count;
    }

}
